using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.AceEngineering.SPM.SPMCustomDataEntity;

namespace Microsoft.AceEngineering.SPM.SPMDataLayer
{
    public class UsersDL
    {
        public static void UpdateInsertUser(Guid userid, string name, string alias, string email, bool isAdmin, bool isActive, string modifiedBy)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[7];
            myParams[0] = new SqlParameter("@UserID", SqlDbType.UniqueIdentifier);
            myParams[0].Value = userid;

            myParams[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 100);
            myParams[1].Value = name;

            myParams[2] = new SqlParameter("@UserEmail", SqlDbType.VarChar, 200);
            myParams[2].Value = email;

            myParams[3] = new SqlParameter("@UserAlias", SqlDbType.VarChar, 50);
            myParams[3].Value = alias;

            myParams[4] = new SqlParameter("@IsAdmin", SqlDbType.Bit);
            myParams[4].Value = isAdmin;

            myParams[5] = new SqlParameter("@Active", SqlDbType.Bit);
            myParams[5].Value = isActive;

            myParams[6] = new SqlParameter("@ModifiedBy", SqlDbType.VarChar, 50);
            myParams[6].Value = modifiedBy;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spUpdateUserByUserID", myParams);
        }

        public static DataSet dsSearchUser(string name, int intSearchType)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[2];
            myParams[0] = new SqlParameter("@SearchName", SqlDbType.VarChar, 50);
            myParams[0].Value = name;

            myParams[1] = new SqlParameter("@SearchType", SqlDbType.Int);
            myParams[1].Value = intSearchType;

            return SqlHelper.ExecuteDataset(conString, "SPM_spSearchUser", CommandType.StoredProcedure, myParams);
        }

        public static DataSet dsSearchUserByGuid(string strGUID)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[1];
            myParams[0] = new SqlParameter("@SearchGUID", SqlDbType.VarChar, 50);
            myParams[0].Value = strGUID;

            return SqlHelper.ExecuteDataset(conString, "SPM_spSearchUserByGUID", CommandType.StoredProcedure, myParams);
        }

        public static UserCDE GetUserByUserAlias(string alias)
        {
            SqlDataReader sqlreader = null;
            UserCDE user = new UserCDE();

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@alias", SqlDbType.VarChar);
                myParams[0].Value = alias;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spGetUserbyUserAlias", myParams);
                sqlreader.Read();
                user.UserAlias = sqlreader["UserAlias"].ToString();
                user.UserEmail = sqlreader["UserEmail"].ToString();
                user.UserName = sqlreader["UserName"].ToString();

            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return user;
        }

        public static UserCDE ValidateUser(string strUserAlias)
        {
            string strConn = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;
            UserCDE user = new UserCDE();
            SqlDataReader sqlreader = null;
            try
            {
                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
                myParams[0].Value = strUserAlias;

                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(strConn, CommandType.StoredProcedure, "SPM_spValidateUser", myParams);
                if (sqlreader.Read())
                {
                    user.UserAlias = sqlreader["UserAlias"].ToString();
                    user.Admin = Convert.ToBoolean(sqlreader["IsAdmin"].ToString());
                    user.Active = true;
                    user.Time = DateTime.Now;
                }
                else
                {
                    user.UserAlias = strUserAlias;
                    user.Active = false;
                    user.Admin = false;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return user;
        }

        public static bool GetUserAdmin(string strUserAlias)
        {
            string strConn = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;
            bool admin = false;
            SqlDataReader sqlreader = null;
            try
            {
                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@alias", SqlDbType.VarChar, 50);
                myParams[0].Value = strUserAlias;

                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(strConn, CommandType.StoredProcedure, "APM_spGetUserAdmin", myParams);
                if (sqlreader.Read())
                {
                    admin = Boolean.Parse(sqlreader["IsAdmin"].ToString());
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return admin;
        }
    }
}
